package my.file.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import org.apache.commons.io.IOUtils;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

/**
 * java执行sql文件
 * @author xsw
 * @date 2018-5-12
 */
public class SqlHelper {
	public static void main(String[] args) {
		String path = "classpath:data.sql";
		String sql = getText(path);
		List<String> sqlarr = getSql(sql);
		for (int i = 0; i < sqlarr.size(); i++) {
			System.out.println(i + ":" + sqlarr.get(i));
		}
		try {
			SqlHelper.execute(getConn("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8","root","scott"), sqlarr);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static Connection getConn(String url,String username,String password) {
		String driver = "com.mysql.jdbc.Driver";
		Connection conn = null;
		try {
			Class.forName(driver); // classLoader,加载对应驱动
			conn = (Connection) DriverManager.getConnection(url, username,
					password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	public static void execute(Connection conn, List<String> sqlFile)
			throws Exception {
		Statement stmt = null;
		stmt = conn.createStatement();
		for (String sql : sqlFile) {
			sql = sql.trim();
			if (sql != null && !sql.equals(""))
				stmt.addBatch(sql);
		}
		int[] rows = stmt.executeBatch();
		System.out.println("Row count:" + Arrays.toString(rows));
		conn.close();
	}

	/*
	 * getText方法吧path路径里面的文件按行读数来放入一个大的String里面去 并在换行的时候加入\r\n
	 */
	public static String getText(String path) {
		if (path.indexOf("classpath:") == 0) {
			StringBuilder ret = new StringBuilder();
			try {
				PathMatchingResourcePatternResolver patternResolver = new PathMatchingResourcePatternResolver();
				Resource resource = patternResolver.getResource(path);
				
				ret.append(IOUtils.toString(resource.getInputStream(), "UTF-8"));
			} catch (IOException e) {
				e.printStackTrace();
			}
			return ret.toString();
		}
		File file = new File(path);
		if (!file.exists() || file.isDirectory()) {
			return null;
		}
		StringBuilder sb = new StringBuilder();
		try {
			FileInputStream fis = new FileInputStream(path);
			InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
			BufferedReader br = new BufferedReader(isr);
			String temp = null;
			temp = br.readLine();
			while (temp != null) {
				if (temp.length() >= 2) {
					String str1 = temp.substring(0, 1);
					String str2 = temp.substring(0, 2);
					if (str1.equals("#") || str2.equals("--")
							|| str2.equals("/*") || str2.equals("//")) {
						temp = br.readLine();
						continue;
					}
					sb.append(temp + "\r\n");
				}

				temp = br.readLine();
			}
			br.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sb.toString();
	}

	/*
	 * getSqlArray方法 从文件的sql字符串中分析出能够独立执行的sql语句并返回
	 */
	public static List<String> getSql(String sql) {
		String s = sql;
		s = s.replaceAll("\r\n", "\r");
		s = s.replaceAll("\r", "\n");
		List<String> ret = new ArrayList<String>();
		String[] sqlarry = s.split(";"); // 用;把所有的语句都分开成一个个单独的句子
		sqlarry = filter(sqlarry);
		ret = Arrays.asList(sqlarry);
		return ret;
	}

	public static String[] filter(String[] ss) {
		List<String> strs = new ArrayList<String>();
		for (String s : ss) {
			if (s != null && !s.equals("")) {
				strs.add(s);
			}
		}
		String[] result = new String[strs.size()];
		for (int i = 0; i < strs.size(); i++) {
			result[i] = strs.get(i).toString();
		}
		return result;
	}
}
